﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Domain;

namespace Agresso.Interface.Layer.Gateway
{
    public class UserSQL
    {
        public static string GetAllUser()
        {
            string sql = "select * from [i_user_master]";
            return sql;
        }
        public static string GetUserByCode()
        {
            string sql = "select TOP 1 * from [i_user_master] WHERE user_code = @usercode";
            return sql;
        }
        public static string GetUserByID()
        {
            string sql = "select TOP 1 * from [i_user_master] WHERE user_id = @userid";
            return sql;
        }
        public static string InsertUser()
        {
            string sql = @"INSERT INTO [i_user_master]
           ([user_code]
           ,[user_name]
           ,[password]
           ,[expiry_date]
           ,[active_code]
           ,[email_id]
           ,[cretn_date]
           ,[cretn_by]
           ,[modfctn_date]
           ,[modfctn_by]
           ,[modfctn_termnl]
            ,[role_master_id]
            )
     VALUES
           (@usercode
           ,@username
           ,@password
           ,@expirydate
           ,@activecode
           ,@emailid
           ,@createndate
           ,@createnby
           ,@modificationdate
           ,@modificationby
           ,@modificationterminal
            ,@rolemasterid)";
            return sql;
        }
        public static string UpdateUser()
        {
            string sql = @"UPDATE [i_user_master]
   SET [user_code] = @usercode
      ,[user_name] = @username
      ,[password] = @password
      ,[expiry_date] = @expirydate
      ,[active_code] = @activecode
      ,[email_id] = @emailid
      ,[modfctn_date] =@modificationdate
      ,[modfctn_by] = @modificationby
      ,[modfctn_termnl] = @modificationterminal
      ,[role_master_id] = @rolemasterid
 WHERE user_id = @userid";
            return sql;
        }
        public static string UpdatePasswordUser()
        {
            string sql = @"UPDATE [i_user_master]
   SET [password] = @password     
      ,[modfctn_date] =@modificationdate
      ,[modfctn_by] = @modificationby
      ,[modfctn_termnl] = @modificationterminal
 WHERE user_id = @userid";
            return sql;
        }
        public static string DeleteUser()
        {
            string sql = @"DELETE FROM [agresso_interface_db].[dbo].[user_table]
 WHERE user_id = @userid";
            return sql;
        }
        public static string InsertLogUser()
        {
            return @"INSERT INTO [agresso_interface_db].[dbo].[user_log_table]
           ([user_id]
           ,[log_in_date]
           ,[session_code]
           ,[remark])
     VALUES
           (@userid, @logindate, @sessioncode, @remark)";
        }
        public static string UpdateLogoutUser()
        {
            return @"UPDATE [agresso_interface_db].[dbo].[user_log_table]
   SET [log_out_date] = @logoutdate     
 WHERE session_code = @sessioncode";
        }
        public static string CountUserLog()
        {
            return @"SELECT COUNT(*)
  FROM [agresso_interface_db].[dbo].[user_log_table] where session_code = @sessioncode";
        }
        public static string InsertRoleMaster()
        {
            return @"INSERT INTO [i_role_master]
           ([role_master_name]
           ,[cretn_date]
           ,[cretn_by]
           ,[modfctn_date]
           ,[modfctn_by])
     VALUES
           (@rolemastername
           ,@cretndate
           ,@cretnby
           ,@modfctndate
           ,@modfctnby)";
        }
        public static string GetMaxRoleMasterID()
        {
            return @"SELECT MAX(role_master_id) FROM [i_role_master]";
        }
        public static string GetRoleMasterByID()
        {
            return @"SELECT * FROM [i_role_master] where role_master_id = @rolemasterid";
        }
        public static string GetAllRoleMaster()
        {
            return @"SELECT * FROM [i_role_master]";
        }
        public static string UpdateRoleMaster()
        {
            return @"UPDATE [i_role_master]
   SET [role_master_name] = @rolemastername
      ,[modfctn_date] = @modfctndate
      ,[modfctn_by] = @modfctnby
 WHERE role_master_id = @rolemasterid";
        }
        public static string DeleteRoleMaster()
        {
            return @"DELETE FROM [i_role_master]
      WHERE  [role_master_id] = @rolemasterid";
        }
        public static string InsertRoleDetil()
        {
            return @"INSERT INTO [i_role_detail]
           ([role_master_id]
           ,[view_flag]
           ,[new_flag]
           ,[update_flag]
            , [form_name])
     VALUES
           (@rolemasterid
           ,@viewflag
           ,@newflag
           ,@updateflag
            ,@formname
            )";
        }
        public static string DeleteRoleDetilByRoleMaster()
        {
            return @"DELETE FROM [i_role_detail]
      WHERE [role_master_id] = @rolemasterid";
        }

        public static string GetRoleDetilByRoleMaster()
        {
            return @"SELECT * FROM [i_role_detail]
      WHERE [role_master_id] = @rolemasterid";
        }
    }
}
